# 补充数据集转换
# domain参数没有以'SUPP'开头
from apps.logger_config import logger

import pandas as pd
from pandas import DataFrame

from apps.parse_vlm import parse_vlm
from apps.query_db import get_value_list
from apps.standard_domain_convert import ct_handling
from apps.utils import convert_to_iso8601


# 实现补充数据集的生成
# path: 输入文件路径
# dataset_df_dic: 数据集DataFrame字典
# project_id: 项目id
# domain: 域名称, 不包含"SUPP"
def supp_domain_convert(path, dataset_df_dic: dict[str, DataFrame], project_id, domain, extra_supp_domain_df_dic):
    logger.info("======开始进行SUPP%s域的转换===========" % domain)
    # 1. 设置固定表头
    var_list = ['STUDYID', 'RDOMAIN', 'USUBJID', 'IDVAR', 'IDVARVAL', 'QNAM', 'QLABEL', 'QVAL', 'QORIG', 'QEVAL']
    data_df = pd.DataFrame(columns=var_list)

    # 2. 获取值级元数据
    supp_domain = 'SUPP' + domain
    value_list = get_value_list(project_id, supp_domain, 'QVAL')

    seq_field = domain + 'SEQ'
    domain_df = dataset_df_dic[domain]

    for index, value_info in value_list.iterrows():
        # 3. 给每个自定义变量生成一个DataFrame
        variable = value_info['variable']
        var_name = value_info['var_name']
        data_source = value_info['data']   # 数据来源
        data_type = value_info['data_type']
        # field = value_info['field']
        try:
            # 3.1 如果是条件填充， 获取条件字段
            add_column_dict = {}  # 需要额外添加的列， 合并df前会删掉
            if value_info['fill_method'] == 1:  # 按条件填充
                fill_description = value_info['fill_description']  # 填充条件
                vlm_component_lst = parse_vlm(fill_description)  # [['SC.SCTESTCD', '=', 'EMPJOB']]
                for vlm_component in vlm_component_lst:
                    op, value = vlm_component[1], vlm_component[2]
                    dom = vlm_component[0].split('.')[0]
                    var = vlm_component[0].split('.')[1]
                    if dom == domain:  # 目前只考虑用于条件判断的列也来自于当前域
                        add_column_dict[var] = [op, value]

            for key, value in add_column_dict.items():
                var_list.append(key)  # 拓展几行表头字段，用于条件填充
            df = pd.DataFrame(columns=var_list)

            # 3.2 生成STUDYID、USUBJID、QVAL、IDVARVAL、QNAM、 QLABEL、QORIG七列
            df['STUDYID'] = domain_df['STUDYID']
            df['USUBJID'] = domain_df['USUBJID']
            df['QVAL'] = domain_df[variable]
            if domain != 'DM':
                df['IDVARVAL'] = domain_df[seq_field]  # 填充--SEQ具体数值
            df.dropna(subset=['QVAL'], inplace=True)  # 去除空行
            df.loc[:, 'QNAM'] = variable  # 填充变量缩写
            df.loc[:, 'QLABEL'] = var_name  # 填充变量名称
            """
            根据data_source字段，填充QORIG
            QORIG根据域变量配置源数据类型填充，C170548-收集填充“CRF”，C170547-分配填充“方案”“Protocol”，
            C170550-前身/C170549-衍生填充“衍生”“Derived”，其他选项不填充。
            """
            if data_source == 'Collected':
                df.loc[:, 'QORIG'] = 'CRF'
            elif data_source == 'Assigned':
                df.loc[:, 'QORIG'] = 'Protocol'
            elif data_source == 'Derived' or data_source == 'Predecessor':
                df.loc[:, 'QORIG'] = 'Derived'
            else:
                df.loc[:, 'QORIG'] = 'CRF'

            # 3.3 生成额外的列
            for add_var in add_column_dict.keys():
                df[add_var] = domain_df[add_var]

            # 3.4 根据条件填充进行筛选
            for key, value in add_column_dict.items():
                df = filter_dataframe(df, key, value[0], value[1])

            # 3.5 处理受控术语替换， 将数值替换为递交值
            try:
                ct_handling(variable, 'QVAL', df, value_list)
            except Exception as e:
                logger.error(f'衍生变量处理出错：{variable} {e}')

            # 3.6 处理超长文本
            df = split_long_rows(df)

            # 4. 合并DataFrame
            # 合并前，删除不需要的列
            df.drop(columns=add_column_dict.keys(), inplace=True)

            # 5. 处理日期时间类型
            if data_type in ['datetime', 'Datetime', 'date', 'Date']:
                df = convert_to_iso8601(df, 'QVAL', data_type)

            data_df = pd.concat([data_df, df])

            for key, value in add_column_dict.items():
                var_list.remove(key)  # 删除拓展的几行表头字段
        except Exception as error:
            print(error)

    # 6. 合并转换标准域时需要写入补充数据集中的内容
    if domain in extra_supp_domain_df_dic.keys():
        extra_supp_domain_df = extra_supp_domain_df_dic[domain]
        data_df = pd.concat([data_df, extra_supp_domain_df])

    # 5. 生成RDOMAIN、IDVAR两列
    # data_df['QORIG'] = 'CRF'
    data_df['RDOMAIN'] = domain
    if domain != 'DM':
        data_df['IDVAR'] = domain + 'SEQ'

    print("填入SUPP", domain, "域的数据为============>\n", data_df)

    if not data_df.empty:
        # 将Dataframe写入excel
        with pd.ExcelWriter(path=path, mode='a') as writer:
            data_df.to_excel(writer, sheet_name=supp_domain, index=False)
        print("=====写入成功=======")


# 条件填充
# 根据op 和 value，对df的column列进行筛选
def filter_dataframe(df, column, op, value):
    if op == '=':
        result_df = df[df[column] == value]
    elif op == '<':
        result_df = df[df[column] < value]
    elif op == '>':
        result_df = df[df[column] > value]
    elif op == '<=':
        result_df = df[df[column] <= value]
    elif op == '>=':
        result_df =df[df[column] >= value]
    elif op == '!=':
        result_df = df[df[column] != value]
    elif op == 'in':
        if not isinstance(value, list):
            raise ValueError("For 'in' operation, value must be a list")
        result_df = df[df[column].isin(value)]
    elif op == 'not in':
        if not isinstance(value, list):
            raise ValueError("For 'not in' operation, value must be a list")
        result_df = df[~df[column].isin(value)]
    else:
        raise ValueError("Unsupported operator")
    return result_df


# 超长文本处理
# QVAL文本长度超过200，则需要截断，并新增一行
# 每200截断一次，放在SUPP--.QVAL中，QNAME=变量缩写+1.2.3...,QLABEL=变量全称
def split_long_rows(df):
    all_new_rows = []
    for idx, row in df.iterrows():
        # text = str(row['QVAL'])
        # text = re.sub(r'[^\w\s]', '', text)  # 删除非字母数字字符
        # 只对字符串类型的QVAL进行处理
        if isinstance(row['QVAL'], str):
            # 检查长度，如果超过200，则分割
            if len(row['QVAL']) > 200:
                qval_parts = [row['QVAL'][i:i + 200] for i in range(0, len(row['QVAL']), 200)]
                for i, part in enumerate(qval_parts):
                    seq = str(i)
                    if i == 0:
                        seq = ''
                    # 复制当前行的其他列值，修改QVAL
                    new_row = row.copy()
                    new_row['QVAL'] = part
                    new_row['QNAM'] = row['QNAM'] + seq  # QNAME=变量缩写+1.2.3
                    all_new_rows.append(new_row)
            else:  # 如果长度不超过200，直接添加当前行的副本
                all_new_rows.append(row.to_dict())
        else:  # 如果QVAL不是字符串（例如是整数或浮点数），直接添加当前行的副本
            all_new_rows.append(row.to_dict())

    # 使用收集到的新行数据创建一个新的DataFrame
    new_df = pd.DataFrame(all_new_rows)
    return new_df


if __name__ == '__main__':
    # 示例使用
    data = {
        'A': [1, 2, 3, 4],
        'B': [5, 6, 7, 8],
        'C': [9, 10, 11, 12],
        'D': ['a', 'b', 'a', 'c']
    }

    df = pd.DataFrame(data)

    # 假设我们要筛选 D 列中值属于 ['a', 'b'] 的行
    filtered_df = filter_dataframe(df, 'D', 'in', ['a', 'b'])
    print(filtered_df)

    # 假设我们要筛选 D 列中值不属于 ['a', 'b'] 的行
    filtered_df = filter_dataframe(df, 'D', 'not in', ['a', 'b'])
    print(filtered_df)